package com.tevintao.marketingsystem.datebase;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;
import android.text.TextUtils;

import com.tevintao.marketingsystem.log.LogManager;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.Random;


public class DeviceInfoDB extends SQLiteOpenHelper {

    //数据库文件在SD卡中,此时必须用全限定名!! 默认路径在/data/data/databases/testDBinSD.sqlite, 但私密数据外界看不到-除非root。
    public static final String SQL_NAME = "/mnt/sdcard/ms/device.sqlite";//数据库名称。
    public static final String MAIN_DATA_TABLE_NAME = "device";//表名。
    //    "imei","imsi","iccid","OS_VERSION","BRAND","UA","IP","PROVINCE","CITY","DISPLAY_WIDTH","DISPLAY_HEIGHT","SDK_INT","MAC_ADDRESS"
//    "862108024196048","460001580655142","8986005615118a024142","4.2.1","OPPO","U707T","120.192.151.54","山东","滨州","1280","720","17","000000000000"
//    "864961024298687","460023942732669","89860029243492726694","4.3","GiONEE","GN9005","112.112.43.209","云南","昭通","1280","720","18","b898f7ea0e0d"
    //建表
    public static final String createDeviceInfoTab = "create table if not exists " + MAIN_DATA_TABLE_NAME + "("
            + "device_name varchar(50), "
            + "device_brand varchar(50), "
            + "device_manufacture varchar(50), "
            + "device_product varchar(50), "
            + "device varchar(50), "
            + "device_type varchar(50), "

            + "width INTEGER, "
            + "height INTEGER, "
            + "device_density FLOAT, "

            + "os_version varchar(50), "
            + "imei varchar(50), "
            + "ua varchar(255)); ";

    //构造方法
    public DeviceInfoDB(Context context) {
        super(context, SQL_NAME, null, 1);
    }


    @Override
    public SQLiteDatabase getWritableDatabase() {
        return super.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createDeviceInfoTab);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    private static Random mRandom = new Random();

//    public static DeviceInfo getRandomDeviceInfo() {
//
//        return null;
//    }

    public static DeviceInfo getRandomDeviceInfo() {
        File dbFile = new File(DeviceInfoDB.SQL_NAME);
        if (!dbFile.exists()) {
            return null;
        }

        String selection = "os_version like ?";
        String[] selectionArgs = null;
        if (Build.VERSION.RELEASE.length() >= 3) {
            String args = Build.VERSION.RELEASE.substring(0, 3);
            selectionArgs = new String[]{args + "%"};
        }


        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
        try {
            Cursor cursor = db.query(DeviceInfoDB.MAIN_DATA_TABLE_NAME, null, selection, selectionArgs, null, null, null);
            int count = cursor.getCount();
            if (count > 0) {
                int index = mRandom.nextInt(count) + 1;
                LogManager.getInstance().logInfo("count = " + count + " index = " + index);
                cursor.move(index);
                String device_name = cursor.getString(cursor.getColumnIndex("device_name"));
                String device_brand = cursor.getString(cursor.getColumnIndex("device_brand"));
                String device_manufacture = cursor.getString(cursor.getColumnIndex("device_manufacture"));
                String device_product = cursor.getString(cursor.getColumnIndex("device_product"));
                String device = cursor.getString(cursor.getColumnIndex("device"));
                String device_type = cursor.getString(cursor.getColumnIndex("device_type"));

                int width = cursor.getInt(cursor.getColumnIndex("width"));
                int height = cursor.getInt(cursor.getColumnIndex("height"));
                float device_density = cursor.getFloat(cursor.getColumnIndex("device_density"));

                String os_version = cursor.getString(cursor.getColumnIndex("os_version"));
                String imei = cursor.getString(cursor.getColumnIndex("imei"));
                String ua = cursor.getString(cursor.getColumnIndex("ua"));

                DeviceInfo info = new DeviceInfo();
                info.setDevice_name(device_name);
                info.setDevice_brand(device_brand);
                info.setDevice_manufacture(device_manufacture);
                info.setDevice_product(device_product);
                info.setDevice(device);
                info.setDevice_type(device_type);

                info.setWidth(width);
                info.setHeight(height);
                info.setDevice_density(device_density);

                info.setOs_version(os_version);
                info.setImei(imei);
                info.setUa(ua);
                return info;
            } else {
                LogManager.getInstance().logError("查找新的设备信息数量为 0 " + selectionArgs);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return null;
    }

    public static boolean insert(DeviceInfo info) {
        File dbFile = new File(SQL_NAME);
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
        long insert = db.insert(MAIN_DATA_TABLE_NAME, null, DeviceInfo.getContentValues(info));
        return insert > 0;
    }


    public static void readLocal() {
        File dbFile = new File(DeviceInfoDB.SQL_NAME);
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
        Cursor cursor = db.query(DeviceInfoDB.MAIN_DATA_TABLE_NAME, null, null, null, null, null, null);

        int count = cursor.getCount();
        LogManager.getInstance().logInfo("count = " + count);

        int countNum = 0;
        while (cursor.moveToNext()) {
            String device_name = cursor.getString(cursor.getColumnIndex("device_name"));
            String device_brand = cursor.getString(cursor.getColumnIndex("device_brand"));
            String device_manufacture = cursor.getString(cursor.getColumnIndex("device_manufacture"));
            String device_product = cursor.getString(cursor.getColumnIndex("device_product"));
            String device = cursor.getString(cursor.getColumnIndex("device"));
            String device_type = cursor.getString(cursor.getColumnIndex("device_type"));

            int width = cursor.getInt(cursor.getColumnIndex("width"));
            int height = cursor.getInt(cursor.getColumnIndex("height"));
            float device_density = cursor.getFloat(cursor.getColumnIndex("device_density"));

            String os_version = cursor.getString(cursor.getColumnIndex("os_version"));
            String imei = cursor.getString(cursor.getColumnIndex("imei"));
            String ua = cursor.getString(cursor.getColumnIndex("ua"));
            LogManager.getInstance().logInfo("device_name = " + device_name + " device_brand = " + device_brand + " device_manufacture = " + device_manufacture + " device_product = " + device_product + " device = " + device + " device_type = " + device_type);
            LogManager.getInstance().logInfo("width = " + width + " height = " + height + " device_density = " + device_density + " os_version = " + os_version + " imei = " + imei + " ua = " + ua);
            countNum++;
        }

        cursor.close();
        db.close();
        LogManager.getInstance().logInfo("countNum = " + countNum);
    }


    public static void readMysql() {
        File file = new File("/mnt/sdcard/ms/d.sql.txt");
//        Toast.makeText(this, "exit = " + file.exists(), Toast.LENGTH_LONG).show();

//        DeviceInfoDB helper = new DeviceInfoDB(CommenApplication.getApplication());
//        SQLiteDatabase db = helper.getWritableDatabase();
//        LogManager.getInstance().logInfo("readMysql db = " + db.close(););

        File dbFile = new File(DeviceInfoDB.SQL_NAME);
        dbFile.delete();
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
        if (null == db) {
            LogManager.getInstance().logError("获取数据库文件失败");
            return;
        }
        db.execSQL(DeviceInfoDB.createDeviceInfoTab);
        BufferedReader reader = null;
        try {

            FileInputStream fis = new FileInputStream(file);
            InputStreamReader isr = new InputStreamReader(fis, "UTF-8"); //指定以UTF-8编码读入
            reader = new BufferedReader(isr);
            String tempString = null;
            int line = 1;
            while ((tempString = reader.readLine()) != null) {
                //把当前行号显示出来
                System.out.println("line " + line + ": " + tempString);
                line++;
                save(db, tempString);
            }
            reader.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        db.close();
    }

    private static final String preStr = "INSERT [dbo].[mymi] ([Id], [uuid], [device_name], [device_brand], [device_manufacture], [device_product], [device], [device_type], [width], [height], [device_density], [os_version], [imei], [ua], [mobile], [CreatedDateTime]) VALUES (";
    private static final String endStr = " CAST(N'";

    private static void save(SQLiteDatabase db, String lineS) {
        if (null != lineS && lineS.startsWith(preStr) && lineS.contains(endStr)) {
            String infoStr = lineS.substring(preStr.length(), lineS.indexOf(endStr) - 1);
//            [Id], [uuid], [device_name], [device_brand], [device_manufacture], [device_product], [device], [device_type], [width], [height], [device_density], [os_version], [imei], [ua], [mobile]
            if (!TextUtils.isEmpty(infoStr)) {
                String[] infos = infoStr.split(",");
                if (infos.length == 16) {
                    String device_name = replace(infos[2]);
                    String device_brand = replace(infos[3]);
                    String device_manufacture = replace(infos[4]);
                    String device_product = replace(infos[5]);
                    String device = replace(infos[6]);
                    String device_type = replace(infos[7]);

                    int width = Integer.parseInt(infos[8].trim());
                    int height = Integer.parseInt(infos[9].trim());
                    float device_density = Float.parseFloat(infos[10].trim());

                    String os_version = replace(infos[11]);
                    String imei = replace(infos[12]);
                    String uaAndEnd = infoStr.substring(infoStr.indexOf(infos[12]) + infos[12].length() + 4);
                    int end = uaAndEnd.indexOf("', N");
                    String ua = "";
//                    String mobile = "";
                    if (end > 0) {
                        ua = replace(uaAndEnd.substring(0, end));
//                        mobile = replace(uaAndEnd.substring(end));
                    }

//                    LogManager.getInstance().logInfo("device_name = " + device_name
//                                    + " device_brand name = " + device_brand
//                                    + " device_manufacture = " + device_manufacture
//                                    + " device_product = " + device_product
//                                    + " device = " + device
//                                    + " device_type = " + device_type
//                                    + " width = " + width
//                                    + " height = " + height
//                                    + " device_density = " + device_density
//                                    + " os_version = " + os_version + "\n"
//                                    + " imei = " + imei + " end = " + end
//                                    + " ua = " + ua
////                            + " mobile = " + mobile
//                    );

                    DeviceInfo info = new DeviceInfo();
                    info.setDevice_name(device_name);
                    info.setDevice_brand(device_brand);
                    info.setDevice_manufacture(device_manufacture);
                    info.setDevice_product(device_product);
                    info.setDevice(device);
                    info.setDevice_type(device_type);

                    info.setWidth(width);
                    info.setHeight(height);
                    info.setDevice_density(device_density);

                    info.setOs_version(os_version);
                    info.setImei(imei);
                    info.setUa(ua);


                    long success = db.insert(DeviceInfoDB.MAIN_DATA_TABLE_NAME, null, DeviceInfo.getContentValues(info));
                    LogManager.getInstance().logInfo("success = " + success);
                }
            }
        }
    }

    private static String replace(String str) {
        String rs = str.replace("N'", "");
        rs = rs.replace("'", "");
        rs = rs.trim();
        return rs;
    }
}
